package com.green.common.util;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelUtil {
	/**
	 * 只取得第0个sheet
	 * 
	 * @param bs
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public static<T> List<T> excelToList(byte[] bs, RowToObject rowToObject) {
		final List<Object> resultList = new ArrayList<Object>();
		final Workbook workbook=getWorkbook(bs);
		final Sheet hssfSheet = workbook.getSheetAt(0);
		final Iterator<Row> rowIter = hssfSheet.rowIterator();
		
		int index=0;
		while (rowIter.hasNext()) {
			final Row row= rowIter.next();
			Object reulstObj =null;
			try {
				 reulstObj = rowToObject.rowToObject(row,index);
			} catch (RuntimeException e) {
				if(e instanceof SkipAnalysisRowToObjectException){
					throw e;
				}else{
					reulstObj = rowToObject.analysisRowToObjectException(e, row,index);
				}
			}finally{
				index++;
			}
			if (reulstObj != null) {
				resultList.add(reulstObj);
			}
		}
		return (List<T>) resultList;
	}
	
	/**
	 * 得到excel  处理 2007 和 2003
	 * @param bs
	 * @return
	 */
	private static Workbook  getWorkbook(byte[] bs){
		ByteArrayInputStream sshIn =null;
		try {
			sshIn = new ByteArrayInputStream(bs);
			return  new HSSFWorkbook(sshIn);
		} catch (Exception e) {
			final ByteArrayInputStream in = new ByteArrayInputStream(bs);
			try {
				return new XSSFWorkbook(in);
			} catch (IOException e1) {
				throw new RuntimeException("取得Workbook出错",e1);
			}finally{
				if(in!=null){
					try {
						in.close();
					} catch (IOException e1) {
					}
				}
			}
		}finally{
			if(sshIn!=null){
				try {
					sshIn.close();
				} catch (IOException e) {
			    }
			}
		}
	} 

	public static abstract class RowToObject {
		public abstract Object rowToObject(Row row,int index);
		
		public Object analysisRowToObjectException(RuntimeException e,Row row,int index){
			throw e;
		}
		
        public Object getCellValue(Cell cell){
        	switch (cell.getCellType()) {
			case Cell.CELL_TYPE_NUMERIC:
				return cell.getNumericCellValue();
			case Cell.CELL_TYPE_STRING:
				return cell.getStringCellValue().trim();
			case Cell.CELL_TYPE_BOOLEAN:
				return cell.getBooleanCellValue();
			default:
				return cell.toString().trim();
			}
		}
        
        public String  getCellStringValue(Cell cell){
        	if(cell==null){
        		return null;
        	}
        	cell.setCellType(Cell.CELL_TYPE_STRING);
        	return cell.toString().trim();
        }

	}
	
	public static class SkipAnalysisRowToObjectException extends RuntimeException{
		private static final long serialVersionUID = 1780511616974168097L;
		
		public SkipAnalysisRowToObjectException(String message,Exception e){
			super(message, e);
		}
		
	}
}
